package br.com.digidata.modelo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.digidata.negocio.Endereco;
import br.com.digidata.negocio.Paciente;

public class PacienteDAO {
	private Connection conn;
	
	private int lastInsertId() {
		int id = -1;
		
        try {
        	
            PreparedStatement stmt = conn.prepareStatement("SELECT last_insert_id() AS id");

            ResultSet rs = stmt.executeQuery();            
            
            rs.next();
            
            id = rs.getInt("id");
                            
            rs.close();
            stmt.close();
            
        } catch (Exception e) {
            System.out.println("Id paciente: " + new java.util.Date() + " " + e);
        }
        
        return id;
	}
	
	public boolean insert(Paciente p) {
        conn = ConexaoBD.getConnection();
        boolean retorno = false;

		try {
			PreparedStatement pstmtEndereco = conn.prepareStatement("INSERT INTO endereco " +
					"(logradouro, numero, complemento, bairro, cidade, estado, cep) " +
					"values (?,?,?,?,?,?,?)");
			
			pstmtEndereco.setString(1, p.getEndereco().getLogradouro());
			pstmtEndereco.setInt(2, p.getEndereco().getNumero());
			pstmtEndereco.setString(3, p.getEndereco().getComplemento());
			pstmtEndereco.setString(4, p.getEndereco().getBairro());
			pstmtEndereco.setString(5, p.getEndereco().getCidade());
			pstmtEndereco.setString(6, p.getEndereco().getEstado());
			pstmtEndereco.setString(7, p.getEndereco().getCep());

			pstmtEndereco.executeUpdate();
			
			PreparedStatement pstmtPaciente = conn.prepareStatement("INSERT INTO paciente " +
					"(endereco_id, cpf, nome, data_nascimento, sexo, telefone_residencial, " +
					"telefone_celular, identidade, convenio) " +
					"values (?,?,?,?,?,?,?,?,?)");
			
			pstmtPaciente.setInt(1, this.lastInsertId());
			pstmtPaciente.setString(2, p.getCpf());
			pstmtPaciente.setString(3, p.getNome());
			pstmtPaciente.setString(4, p.getDataNascimento());
			pstmtPaciente.setString(5, p.getSexo());
			pstmtPaciente.setString(6, p.getTelefoneResidencial());
			pstmtPaciente.setString(7, p.getTelefoneCelular());
			pstmtPaciente.setInt(8, p.getIdentidade());
			pstmtPaciente.setString(9, p.getConvenio());
           
			pstmtPaciente.executeUpdate();
			
		    retorno = true;
			
		    pstmtEndereco.close();
		    pstmtPaciente.close();
			conn.close();
		} catch (SQLException e) {
			System.out.println("Incluir paciente: " + new java.util.Date() + " " + e);
			retorno = false;
		}
		return retorno;
	}

	public void update(Paciente p) {
		try{
            conn = ConexaoBD.getConnection();
            
            PreparedStatement stmt = conn.prepareStatement("UPDATE paciente SET convenio = ?, telefone_celular = ? WHERE id = ? ");
            stmt.setString(1, p.getConvenio());
            stmt.setString(2, p.getTelefoneCelular());
            stmt.setInt(3, p.getId());
            stmt.executeUpdate();
            
            stmt.close();
            conn.close();
            
        } catch (Exception e){
            System.out.println("Alterar paciente: " + new java.util.Date() + " " + e);
        }
	}
	
	public List<Paciente> retrive(){
        List<Paciente> pacientes = new ArrayList<Paciente>();
        
        try {
            conn = ConexaoBD.getConnection();
        	
            Statement stmt = conn.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT * FROM paciente p JOIN endereco e ON p.endereco_id = e.id");
            
            while (rs.next()) {
            	Endereco endereco = new Endereco(rs.getInt("id"), 
            			rs.getString("logradouro"), 
            			rs.getInt("numero"), 
            			rs.getString("complemento"), 
            			rs.getString("bairro"), 
            			rs.getString("cidade"), 
            			rs.getString("estado"), 
            			rs.getString("cep"));
            	
                Paciente paciente = new Paciente(rs.getInt("id"), 
                		rs.getString("cpf"), 
                		rs.getString("nome"), 
                		rs.getString("data_nascimento"), 
                		rs.getString("sexo"),
                		rs.getString("telefone_residencial"),
                		rs.getString("telefone_celular"), 
                		endereco, 
                		rs.getInt("identidade"), 
                		rs.getString("convenio"));
                
                pacientes.add(paciente);
            }
            
            rs.close();
            stmt.close();
            conn.close();
            
        } catch (Exception e) {
            System.out.println("Listar pacientes: " + new java.util.Date() + " " + e);
        }
        
        return pacientes;
    }

	public Paciente getById(int id) {
	    
			Paciente paciente = null;
			
	        try {
	            conn = ConexaoBD.getConnection();
	        	
	            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM paciente p JOIN endereco e ON p.endereco_id = e.id WHERE id = ? ");
	            stmt.setInt(1, id);

	            ResultSet rs = stmt.executeQuery();            
	            
	            rs.next();
	            
	        	Endereco endereco = new Endereco(rs.getInt("id"), 
	        			rs.getString("logradouro"), 
	        			rs.getInt("numero"), 
	        			rs.getString("complemento"), 
	        			rs.getString("bairro"), 
	        			rs.getString("cidade"), 
	        			rs.getString("estado"), 
	        			rs.getString("cep"));
	        	
	            paciente = new Paciente(rs.getInt("id"), 
	            		rs.getString("cpf"), 
	            		rs.getString("nome"), 
	            		rs.getString("data_nascimento"), 
	            		rs.getString("sexo"),
	            		rs.getString("telefone_residencial"),
	            		rs.getString("telefone_celular"), 
	            		endereco, 
	            		rs.getInt("identidade"), 
	            		rs.getString("convenio"));
	                            
	            rs.close();
	            stmt.close();
	            conn.close();
	            
	        } catch (Exception e) {
	            System.out.println("Pegar paciente: " + new java.util.Date() + " " + e);
	        }
	        
	        return paciente;	
	}
}
